package com.es.hr.manage;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.hr.emp.EmpInfoBean;
import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class ManageBiz {

	public PageBean queryEmpList(PageNavigator pageNav, ArrayList<String> param)
			throws Exception {
		Dao dao = new Dao();
		PageBean pageBean = new PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select emp.emp_id,emp.emp_area_value,ltrim(rtrim(emp.emp_login_yn)) emp_login_yn,emp.emp_province_value,emp.emp_city_value,emp.emp_name,emp.emp_login_id,(select top 1 common_value from hrm_common "
					+ "where common_code = emp.emp_role and common_type='role') emp_role," 
					+ "(select top 1 a.emp_name from hrm_emp_info a where a.emp_ctm_code = emp.emp_id) ctm_name,"
					+ "emp.emp_code,emp.emp_job,emp.emp_phone_mobile,emp.emp_phone_native,emp.emp_mail,emp.shop_name," 
					+" emp.emp_city_level,emp.emp_shiyebu,emp.emp_rtm_name,emp.emp_level,emp.itmall_city,emp.itmall_city_num,emp.c3_city,emp.c3_city_num  "
					+ " from hrm_emp_info emp where emp.del_flag='N' and (emp.emp_lizhi_status is null or emp.emp_lizhi_status='N')";
			String[] sqlArrary = new String[] { "and emp.emp_area_code=? ",
					"and emp.emp_province_code=? ", "and emp.emp_city_code=? ",
					"and emp.emp_shiyebu = ?",
					"and emp.emp_role=? ",
					"and UPPER(emp.emp_name) like '%'+UPPER(?)+'%'",
					"and UPPER(emp.emp_code) like '%'+UPPER(?)+'%'",
					"and UPPER(emp.emp_job) like '%'+UPPER(?)+'%'",
					" and emp.emp_rtm_code=?",
					" and (emp.emp_id=?  or  emp.emp_role not in(?,?,?,?,?))"};
			for (int i = 0; i < param.size(); ++i) {
				if (!param.get(i).equals("")&&i<9) {
						sql += sqlArrary[i];
						query.add(param.get(i).trim());
				}
			}
			if(param.size()>9){
				sql += sqlArrary[9];
				query.add(param.get(9).trim());
				
				query.add(param.get(10).trim());
				query.add(param.get(11).trim());
				query.add(param.get(12).trim());
				query.add(param.get(13).trim());
				query.add(param.get(14).trim());
			}
			int total = dao.getAllRow(sql, query);
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total % pageNav.getPageRow() == 0 ? total
					/ pageNav.getPageRow() : total / pageNav.getPageRow() + 1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally {
			dao.close();
		}
		return pageBean;
	}
	
	@SuppressWarnings("unchecked")
	public boolean updateEmpInfoDo(EmpInfoBean bean) throws Exception{
		Dao dao = new Dao();
		ArrayList query = new ArrayList();
		String extraField = "emp_id,emp_info_bakup,emp_lizhi_yuanyin,emp_lizhidan_yn,emp_lizhi_date";
		StringBuffer sb = new StringBuffer();
		Field[] fds = Class.forName("com.es.hr.emp.EmpInfoBean").getDeclaredFields();   
		 
		for(int i=0;i<fds.length;i++){   
			if(fds[i].getName().equals("emp_login_id")){
				sb.append(fds[i].getName()+"=?,");
				query.add(bean.getAiwang());
			}else if(fds[i].getName().equals("emp_login_yn")/*&&bean.getEmp_role().equals(anObject)*/){
				sb.append(fds[i].getName()+"=?,");
				query.add("Y");
			}else if(fds[i].getName().equals("emp_city_level")){
				
			}else if(fds[i].getName().equals("emp_ctm_name")){//不需增加
			}else if(extraField.indexOf(fds[i].getName())<0){
				sb.append(fds[i].getName()+"=?,");
				query.add((String)fds[i].get(bean));
			} 	  
		}		
		query.add(String.valueOf(bean.getEmp_id()));
		try {
			String sql = "update hrm_emp_info set "+ sb.toString().substring(0, sb.toString().length()-1)+" where emp_id=?";
			dao.update(sql,query);
		} catch(Exception e){
				System.out.print(e.getMessage());
			   return false;
		   }finally {		   
			   dao.close();
		    }
		    return true;
	}
	
	@SuppressWarnings("unchecked")
	public boolean deleteEmpInfoDo(String emp_id ) throws Exception{
		if(emp_id==null) return false;
		Dao dao = new Dao();
		ArrayList query = new ArrayList();
		query.add(emp_id);
		try {
			String sql = "update hrm_emp_info set del_flag='Y' where emp_id=?";
			dao.update(sql,query);
		} catch(Exception e){
				System.out.print(e.getMessage());
			   return false;
		   }finally {		   
			   dao.close();
		    }
		    return true;
	}
	
	public ArrayList<HashMap<String,String>> exceptExcelEMpList(ArrayList<String> param)
		throws Exception {
		Dao dao = new Dao();
		ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
		ArrayList<String> query = new ArrayList<String>();
	
		try {
			String sql = "select emp.*,"
				+"(select top 1 common_value from hrm_common where common_code = emp.emp_role and common_type='role') emp_role_name, "
				+"(select top 1 a.emp_name from hrm_emp_info a where  emp.emp_ctm_code = a.emp_id) emp_ctm_name,"
				+"(select top 1 city_level from hrm_city where city_name = emp.emp_city_value) emp_ctm_level_name "
				+"from hrm_emp_info emp where emp.del_flag='N' ";
			String[] sqlArrary = new String[] { "and emp.emp_area_code=? ",
					"and emp.emp_province_code=? ", "and emp.emp_city_code=? ",
					"and emp.emp_shiyebu = ?",
					"and emp.emp_role=? ",
					"and UPPER(emp.emp_name) like '%'+UPPER(?)+'%'",
					"and UPPER(emp.emp_code) like '%'+UPPER(?)+'%'",
					"and UPPER(emp.emp_job) like '%'+UPPER(?)+'%'",
					" and emp.emp_rtm_code=?",
					" and (emp.emp_id=?  or  emp.emp_role not in(?,?,?,?,?))"};
			for (int i = 0; i < param.size(); ++i) {
				if (!param.get(i).equals("")&&i<9) {
						sql += sqlArrary[i];
						query.add(param.get(i).trim());
				}
			}
			if(param.size()>9){
				sql += sqlArrary[9];
				query.add(param.get(9).trim());
				
				query.add(param.get(10).trim());
				query.add(param.get(11).trim());
				query.add(param.get(12).trim());
				query.add(param.get(13).trim());
				query.add(param.get(14).trim());
			}
			ResultSet resultSet = null;
			if(query.size()==0)
				resultSet = dao.Select(sql);
			else
				resultSet = dao.Select(sql,query);
			ResultSetMetaData rss = resultSet.getMetaData();
			
			while(resultSet.next()){
					HashMap<String,String> map = new HashMap<String,String>();
					for(int i = 0;i<rss.getColumnCount(); i++){ 	
						if(rss.getColumnName(i+1).equals("emp_role_name"))
							map.put("emp_role", resultSet.getString("emp_role_name"));
						else if(rss.getColumnName(i+1).equals("emp_ctm_level_name"))
							map.put("emp_ctm_level", resultSet.getString("emp_ctm_level_name"));
						else if(!rss.getColumnName(i+1).equals("emp_role")
							  	&&!rss.getColumnName(i+1).equals("emp_ctm_level")
							  	&&!rss.getColumnName(i+1).equals("emp_id"))
							map.put(rss.getColumnName(i+1), (String) resultSet.getObject(rss.getColumnName(i+1)));
				 }	
				  list.add(map);
			  }
		} finally {
			dao.close();
		}
		return list;
		}
	
	/*private void EmpInfoBeanSet(EmpInfoBean bean,String field,String value) throws SecurityException, ClassNotFoundException, IllegalArgumentException, IllegalAccessException{
		Field[] fds = Class.forName("com.es.hr.emp.EmpInfoBean").getDeclaredFields();   
		
		for(int i=0;i<fds.length;i++){   
			if(fds[i].getName().equals(field)){
				fds[i].set(bean, value);
				return;
			}
		}		
	}
	
	private HashMap getEmpBeanMap(EmpInfoBean bean) throws SecurityException, ClassNotFoundException, IllegalArgumentException, IllegalAccessException{
			
		return null;
	}*/
	
}
